import os
import time
import xlrd
import xlsxwriter
from xlsxwriter.exceptions import FileCreateError
from save_histroy import save_tableName_to_file
from get_name import get_table_name


all_list = []


class ReadXls(object):
    def __init__(self, filepath):
        self.data = xlrd.open_workbook(filepath)
        self.names = self.data.sheet_names()
        self.t_data = {}

    def read_xls_head(self):
        for name in self.names:
            t_common_header = []
            table = self.data.sheet_by_name(name)
            self.data.sheet_loaded(name)

            """取公共表头"""
            for i in range(3):
                t_common_header.append(table.row_values(i))

            t_common_header[-1].insert(0, "档号")
            self.t_data["common_header"] = t_common_header

        all_list.append(self.t_data)
        return all_list

    def read_xls_body(self):
        for name in self.names:
            sheet_data = []
            table = self.data.sheet_by_name(name)
            self.data.sheet_loaded(name)
            nrows = table.nrows
            ncols = table.ncols

            """取数据"""
            i = 0
            for nrow in range(nrows):
                while i < nrows:
                    if table.row_values(i)[0] == "":
                        for ncol in range(ncols):
                            if table.row_values(i)[ncol] != "":
                                sheet_data[-1][ncol+1] += str(table.row_values(i)[ncol])
                                print("合并单元格：", "(" + str(i) + "," + str(ncol) + "),(" + str(i + 1) + "," + str(ncol) + ")")

                        i += 1
                        continue
                    elif str(table.row_values(i)[0])[0:2] == "归档":
                        i += 3

                    sheet_data.append(table.row_values(i))
                    sheet_data[-1].insert(0, name)
                    i += 1

            self.t_data[name] = sheet_data

        all_list.append(self.t_data)
        return all_list

    def save_to_file(self, result):
        f = xlsxwriter.Workbook("..\\AFresult.xlsx")
        sheet1 = f.add_worksheet("Sheet1")
        r = 0
        for i in result:
            for j in i.values():
                for k in j:
                    c = 0
                    for l in k:
                        sheet1.write(r, c, l)
                        c += 1
                    r += 1

        f.close()


if __name__ == "__main__":
    start = time.time()

    print("----------开始扫描表头----------")
    try:
        test = ReadXls(get_table_name()[0])
        print("已从", get_table_name()[0], "获取")
        test.read_xls_head()
        print("----------表头扫描结束----------")
    except IndexError:
        print("扫描结束，请检查当前文件夹是否有目标文件！")
        exit()
    except ValueError:
        print("扫描结束，请检查文件名是否为数字类型！或关闭当前文件夹下的已打开的文件！")
        exit()
    except FileNotFoundError:
        print("扫描结束，请检查当前文件夹是否有文件！")
        exit()

    i = 1
    print("\n----------开始扫描数据----------")
    try:
        print("已获取到", len(get_table_name()), "个表格，开始提取数据")

        for filepath in get_table_name():
            print("\n总进度：", str(i) + "/" + str(len(get_table_name())), "\t\t\t", "表名：", get_table_name()[get_table_name().index(filepath)])
            test = ReadXls(filepath)
            result = test.read_xls_body()
            test.save_to_file(result)
            save_tableName_to_file(get_table_name()[get_table_name().index(filepath)])
            i += 1
        print("\n----------数据扫描结束----------")
    except ValueError:
        print("请保证文件名为数字类型！")
        exit()
    except FileNotFoundError:
        print("请检查当前文件夹是否有文件！")
        exit()
    except FileCreateError:
        print("提取数据失败，请关闭与result文件同名的表格文件！")
        exit()

    end = time.time()
    print("总计运行时长：" + str(round(end-start, 2)) + "秒")

    os.system("pause")
